﻿Imports System.Data.SqlClient
Public Class FormSale
    Dim DA As SqlClient.SqlDataAdapter
    Dim CB As SqlClient.SqlCommandBuilder
    Dim TBL As DataTable
    Dim rowIndex As Integer = 0
    Dim j As Integer = 1
    Dim su As Decimal = 0
    Dim Constr As String = "server=WINCTRL-B8GV7ME;uid=sa;pwd=123;database=AppSaleDb;integrated security=true; "
    Private Sub FormSale_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtpDATE.ToString()
        dtpDATE.Enabled = False
        'Dim cn As New SqlConnection("server=.\SOPHORN;uid=sa;pwd=123;database=AppSaleDb;")
        'cn.Open()
        'Dim cmd As New SqlCommand("SELECT * FROM tbl_Customer;", cn)
        'Dim dr = cmd.ExecuteReader
        'Dim dt As New DataTable
        'dt.Load(dr)
        'dr.Close()
        ''==========================ClearText Box Customers======================================
        'txtCustomer.Text = ""
        'txtPhone.Text = ""
        'txtAddr.Text = ""



        'GenerateNewNo()

        DA = New SqlClient.SqlDataAdapter("Select * from tbl_SaleDetiles", Constr)
        CB = New SqlClient.SqlCommandBuilder(DA)
        TBL = New DataTable
        DA.Fill(TBL)
        If TBL.Rows.Count >= 0 Then
            showDataInvoiceNo(rowIndex)
        End If
    End Sub
    Sub showDataInvoiceNo(ByVal i As Integer)
        txtInvoiceNo.Text = Val(TBL.Rows.Count) + 1
    End Sub
    Private Sub GenerateNewNo()
        'Dim cn As New SqlConnection()
        'cn.ConnectionString = "server=.\SOPHORN;uid=sa;pwd=123;database=AppSaleDb;"
        'cn.Open()

        'Dim obj As New SqlCommand()
        'obj.Connection = cn
        'obj.CommandText = "SELECT MAX(InvoiceNo) FROM tbl_SaleDetiles"
        'Dim reader As SqlDataReader = obj.ExecuteReader()
        '' Dim cmd As New SqlCommand("SELECT MAX(InvoiceNo) FROM tbl_SaleDetiles")
        'Dim lastNo As String

        'If obj Is DBNull.Value Then
        '    lastNo = "INV-000000"
        'Else
        '    lastNo = obj
        'End If

        'Dim tmp As String = lastNo.Replace("INV-", "")
        'Dim nextNumber As Integer = Val(tmp) + 1
        'Dim nextNo = nextNumber.ToString("000000")
        'nextNo = "INV-" & nextNo

        'Me.txtInvoiceNo.Text = nextNo


    End Sub


    'Private Sub txtCustomer_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles txtCustomer.KeyDown
    '    If e.KeyData = Keys.Enter Then
    '        ShowCustomers()
    '    End If
    'End Sub

    'Public Sub ShowCustomers()
    '    Dim cn As New SqlConnection("server=.\SOPHORN;uid=sa;pwd=123;database=AppSaleDb;")
    '    cn.Open()
    '    Dim cmd As New SqlCommand("SELECT * FROM tbl_Customer WHERE Name=N'" & txtCustomer.Text & "'")
    '    Dim dr = cmd.ExecuteScalar
    '    Dim dt As New DataTable
    '    dt.Load(dr)
    '    dr.Close()

    '    If dt.Rows.Count = 0 Then
    '        MsgBox("Please Insert Name Customers!!!!")
    '    Else
    '        Me.txtPhone.Text = dt.Rows(2)("Phone")
    '        Me.txtAddr.Text = dt.Rows(3)("Address")
    '    End If
    'End Sub
    'Public Sub ShowProducts()
    '    Dim cn As New SqlConnection("server=.\SOPHORN;uid=sa;pwd=123;database=AppSaleDb;")
    '    cn.Open()
    '    Dim cmd As New SqlCommand("SELECT * FROM tbl_Products WHERE Code=N'" & txtCode.Text & "'", cn)
    '    Dim dr = cmd.ExecuteScalar
    '    Dim dt As New DataTable
    '    dt.Load(dr)
    '    dr.Close()

    '    If dt.Rows.Count = 0 Then
    '        MsgBox("Please Insert Products Code!!!!")
    '    Else
    '        Me.txtNamePro.Text = dt.Rows(0)("Name")
    '        Me.txtPriceOut.Text = dt.Rows(0)("PriceOut")
    '    End If
    'End Sub
    'Private Sub txtCode_keyDow(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCode.KeyDown
    '    If e.KeyData = Keys.Enter Then
    '        ShowProducts()
    '    End If
    'End Sub

    Public Sub AddLine()
        Dim i = DataGridViewSale.Rows.Add()
        Dim row As DataGridViewRow = DataGridViewSale.Rows(i)
        row.Cells("Code").Value = txtCode.Text
        row.Cells("NamePro").Value = txtNamePro.Text
        row.Cells("Qty").Value = txtQty.Text
        row.Cells("PriceOut").Value = txtPriceOut.Text
        row.Cells("TotalOut").Value = txtTotal.Text

        txtInvoiceNo.Text = ""
        txtCode.Text = ""
        txtNamePro.Text = ""
        txtQty.Text = ""
        txtPriceOut.Text = ""
        txtTotal.Text = ""
        'txtAmount.Text = ""
        'ComboBoxVAT.Focus()

        CalculateTotal()
    End Sub
    Public Sub CalculateTotal()
        Dim Amount As Decimal = 0.0
        For Each dgr As DataGridViewRow In DataGridViewSale.Rows
            Amount += dgr.Cells("TotalOut").Value
        Next
        txtAmount.Text = Amount
        Dim discount As Decimal = Val(ComboBoxVAT.Text)
        Dim total As Decimal = Amount * (1 + discount / 100)
        txtTotals.Text = total

        '  MsgBox("សូមបញ្ចូលទិន្នន័យឲ្យគ្រប់គ្រាន់")
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        txtCustomer.Text = ""
        txtPhone.Text = ""
        txtAddr.Text = ""

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim cn As New SqlConnection
        cn.ConnectionString = "Server=.;uid=sa;pwd=123;database=AppSaleDb"
        cn.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = cn
        Dim cmdup As New SqlCommand
        cmdup.Connection = cn
        For row = 0 To DataGridViewSale.Rows.Count - 2
            Code = DataGridViewSale.Rows(row).Cells(2).Value
            NamePro = DataGridViewSale.Rows(row).Cells(3).Value
            PriceOut = DataGridViewSale.Rows(row).Cells(4).Value
            Qty = DataGridViewSale.Rows(row).Cells(3).Value
            cmd.CommandText = "INSERT INTO tblSale VALUES('" & txtInvoiceNo.Text & "','" & txtCode.Text & "','" & txtNamePro.Text & "','" & txtQty.Text & "','" & txtPriceOut.Text & "','" & txtTotal.Text & "'," & txtAmount.Text & "," & txtTotals.Text & ")"
            cmdup.CommandText = "UPDATE tbl_Products SET Qty=Qty-" & txtQty.Text & " Where Code='" & txtCode.Text & "' "
            cmd.ExecuteReader()
            cmdup.ExecuteReader()
        Next row
        MsgBox("Sucess")
        cn.Close()
        'DataGridViewSale.Rows.Clear()

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        AddLine()
    End Sub

    Private Sub ComboBoxVAT_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxVAT.SelectedIndexChanged
        CalculateTotal()
    End Sub
    Sub showDataCustomer(ByVal i As Integer)
        For i = 0 To TBL.Rows.Count - 1 Step 1
            If txtCustomer.Text = TBL.Rows(i).Item(1) Then
                txtPhone.Text = TBL.Rows(i).Item(2)
                txtAddr.Text = TBL.Rows(i).Item(3)

            End If
        Next
    End Sub
    Private Sub txtCustomer_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtCustomer.TextChanged
        DA = New SqlClient.SqlDataAdapter("Select * from tbl_Customer", Constr)
        CB = New SqlClient.SqlCommandBuilder(DA)
        TBL = New DataTable
        DA.Fill(TBL)
        If TBL.Rows.Count > 0 Then
            showDataCustomer(rowIndex)
        End If
    End Sub

    Private Sub txtQty_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtQty.TextChanged
        txtTotal.Text = Val(txtQty.Text) * Val(txtPriceOut.Text)
    End Sub

    Sub showDataPro(ByVal i As Integer)
        For i = 0 To TBL.Rows.Count - 1
            If txtCode.Text = TBL.Rows(i).Item(0) Then
                'MsgBox("DADSAD")
                txtNamePro.Text = TBL.Rows(i).Item(1)
                txtPriceOut.Text = TBL.Rows(i).Item(5)

            End If
        Next
    End Sub
    Private Sub txtCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtCode.TextChanged
        DA = New SqlClient.SqlDataAdapter("Select * from tbl_Products", Constr)
        CB = New SqlClient.SqlCommandBuilder(DA)
        TBL = New DataTable
        DA.Fill(TBL)
        If TBL.Rows.Count > 0 Then
            showDataPro(rowIndex)
            'MsgBox("ABC")
        End If
    End Sub
End Class

